package com.sapient;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

/**
 * Servlet implementation class MarketApprovalServlet
 */
@WebServlet("/MarketApprovalServlet")
public class MarketApprovalServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public MarketApprovalServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		
		try {	
			
			String[][] result=null;
			int count = 0;
		String url = "jdbc:oracle:thin:@10.207.60.85:1521/XE";
		String username = "surv";
		String password = "surv";
		String[] marketID;
		
		Class.forName("oracle.jdbc.driver.OracleDriver");
			Connection conn = DriverManager.getConnection(url, username,password);
			Statement st = conn.createStatement();
			
			
		HttpSession session= request.getSession(true);
	
		
			int rows=0;		
			String SQL="Select * from Market where status='P'";// Need to create Mapping Approval table in DB(Walker)
			ResultSet rs=st.executeQuery(SQL);
			while(rs.next()){
				
				rows++;
			}
			result = new String[rows][8];
			marketID = new String[rows];
			rs=st.executeQuery(SQL);
			
			while(rs.next())
			{	
				System.out.println(rs.getString("marketID"));
				marketID[count] = rs.getString("marketID");
				result[count][0] = rs.getString("marketID");
				result[count][1] = rs.getString("MarketName");
				result[count][2] = rs.getString("Location");
				result[count][3] = rs.getString("CommodityType");
				result[count][4] = rs.getString("Currency");
				result[count][5] = rs.getString("startdate");
				result[count][6] = rs.getString("enddate");
				result[count][7] = rs.getString("status");
				
				count++; 
				System.out.println(rs.getString("marketID")+ rs.getString(2));
			}
			
			request.setAttribute("result", result);
			session.setAttribute("marketID", marketID);
		RequestDispatcher r = getServletContext().getRequestDispatcher("/MarketApproval.jsp");
		r.include(request, response);
			
		conn.close();
	
		}
		catch (Exception e) {
			System.err.println("Got an exception! ");
			System.err.println(e.getMessage());
			e.printStackTrace();
		}
		
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		//String[] status=request.getParameterValues("status");
		HttpSession session= request.getSession(true);
		//session.setAttribute("status", status[0]);
		
		String[] marketID =(String[]) session.getAttribute("marketID");
		for(String s: marketID){
			String value =(String) request.getParameter(s);
			if(value!=null){
				try
				{ 
					Class.forName("oracle.jdbc.driver.OracleDriver");
					Connection conn = DriverManager.getConnection(Config.url,Config.username,Config.password); 
					Statement st = conn.createStatement(); 

					String SQL = "";

					SQL = "update Market set  status ='"+value+"' where marketID = "+Integer.parseInt(s);
					st.executeUpdate(SQL);

					conn.close(); 
				} catch (Exception e) { 
					System.err.println("Got an exception! "); 
					System.err.println(e.getMessage()); 
					e.printStackTrace();
				} 
				
				
			}
			
		}
		RequestDispatcher r = getServletContext().getRequestDispatcher("/MainApproval.jsp");
		r.forward(request, response);


	}

}
